﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class ProductClassConverter
    {
        private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;
        public static Dictionary<int, String> LotsCategoryIDToNameMap;
        public static HashSet<String> AddedCategories;
        public static Dictionary<int, String> ConvertProductClass(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            LotsCategoryIDToNameMap = new Dictionary<int, string>();
            AddedCategories = new HashSet<string>();
            DataTable CategoriesDT = DataTables.CreateCategoriesDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT ProductClassID, UCASE(ProductClassName) AS ProductClassName FROM ProductClass");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                            ProductClass productClass = new ProductClass();
                            int i = 0;
                            productClass.ProductClassID = reader.GetInt32(i);
                            productClass.ProductClassName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            SaveCategories(productClass, CategoriesDT);

                        if (CategoriesDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveAllDataTables(CpiamConnectionString, CategoriesDT);
                        }
                    }
                    SaveAllDataTables(CpiamConnectionString, CategoriesDT);
                }
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert ProductClass ", ex);
                MessageBox.Show("Error converting ProductClass: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }

            return LotsCategoryIDToNameMap;
        }

        private static void SaveAllDataTables(String ConnectionString, DataTable DebtorGroupsDT)
        {
            if (DebtorGroupsDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(DebtorGroupsDT, "Categories", ConnectionString);
        }

        private static void SaveCategories(ProductClass productClass, DataTable CategoriesDT)
        {

            if (!String.IsNullOrEmpty(productClass.ProductClassName))
            {
                string newName = productClass.ProductClassName.ToUpper().Trim();
                if (newName.Length > 100)
                    newName = newName.Substring(0, 100);

                if (!AddedCategories.Contains(newName))
                {
                    Categories categories = new Categories(productClass.ProductClassName);
                    Categories.SaveCategoriesToDataTable(categories, CategoriesDT);
                    //CategoriesDT.Rows.Add(productClass.ProductClassName);
                }

                LotsCategoryIDToNameMap[productClass.ProductClassID] = newName;
            }
        }

    }
}
